{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SYS_NAME</th>\n",
       "      <th>NAME</th>\n",
       "      <th>TARGET_ID</th>\n",
       "      <th>DESCRIPTION</th>\n",
       "      <th>ENTITY</th>\n",
       "      <th>VALUE</th>\n",
       "      <th>COLLECTTIME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>CWXT_DB</td>\n",
       "      <td>184</td>\n",
       "      <td>磁盘已使用大小</td>\n",
       "      <td>C:\\</td>\n",
       "      <td>3.427079e+07</td>\n",
       "      <td>2014-10-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>CWXT_DB</td>\n",
       "      <td>184</td>\n",
       "      <td>磁盘已使用大小</td>\n",
       "      <td>D:\\</td>\n",
       "      <td>8.026259e+07</td>\n",
       "      <td>2014-10-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>CWXT_DB</td>\n",
       "      <td>183</td>\n",
       "      <td>磁盘容量</td>\n",
       "      <td>C:\\</td>\n",
       "      <td>5.232332e+07</td>\n",
       "      <td>2014-10-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>CWXT_DB</td>\n",
       "      <td>183</td>\n",
       "      <td>磁盘容量</td>\n",
       "      <td>D:\\</td>\n",
       "      <td>1.572833e+08</td>\n",
       "      <td>2014-10-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>CWXT_DB</td>\n",
       "      <td>184</td>\n",
       "      <td>磁盘已使用大小</td>\n",
       "      <td>C:\\</td>\n",
       "      <td>3.432890e+07</td>\n",
       "      <td>2014-10-02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  SYS_NAME     NAME  TARGET_ID DESCRIPTION ENTITY         VALUE COLLECTTIME\n",
       "0   财务管理系统  CWXT_DB        184     磁盘已使用大小    C:\\  3.427079e+07  2014-10-01\n",
       "1   财务管理系统  CWXT_DB        184     磁盘已使用大小    D:\\  8.026259e+07  2014-10-01\n",
       "2   财务管理系统  CWXT_DB        183        磁盘容量    C:\\  5.232332e+07  2014-10-01\n",
       "3   财务管理系统  CWXT_DB        183        磁盘容量    D:\\  1.572833e+08  2014-10-01\n",
       "4   财务管理系统  CWXT_DB        184     磁盘已使用大小    C:\\  3.432890e+07  2014-10-02"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# -*- coding:utf-8 -*-\n",
    "# 数据预处理\n",
    "#---* 1 *---数据清洗\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from pandas import DataFrame,Series\n",
    "\n",
    "inputfile1 = 'discdata.xls'\n",
    "data = pd.read_excel(inputfile1)\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SYS_NAME</th>\n",
       "      <th>NAME</th>\n",
       "      <th>TARGET_ID</th>\n",
       "      <th>DESCRIPTION</th>\n",
       "      <th>ENTITY</th>\n",
       "      <th>VALUE</th>\n",
       "      <th>COLLECTTIME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>CWXT_DB</td>\n",
       "      <td>184</td>\n",
       "      <td>磁盘已使用大小</td>\n",
       "      <td>C:\\</td>\n",
       "      <td>3.427079e+07</td>\n",
       "      <td>2014-10-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>CWXT_DB</td>\n",
       "      <td>184</td>\n",
       "      <td>磁盘已使用大小</td>\n",
       "      <td>D:\\</td>\n",
       "      <td>8.026259e+07</td>\n",
       "      <td>2014-10-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>CWXT_DB</td>\n",
       "      <td>183</td>\n",
       "      <td>磁盘容量</td>\n",
       "      <td>C:\\</td>\n",
       "      <td>5.232332e+07</td>\n",
       "      <td>2014-10-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>CWXT_DB</td>\n",
       "      <td>183</td>\n",
       "      <td>磁盘容量</td>\n",
       "      <td>D:\\</td>\n",
       "      <td>1.572833e+08</td>\n",
       "      <td>2014-10-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>CWXT_DB</td>\n",
       "      <td>184</td>\n",
       "      <td>磁盘已使用大小</td>\n",
       "      <td>C:\\</td>\n",
       "      <td>3.432890e+07</td>\n",
       "      <td>2014-10-02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  SYS_NAME     NAME  TARGET_ID DESCRIPTION ENTITY         VALUE COLLECTTIME\n",
       "0   财务管理系统  CWXT_DB        184     磁盘已使用大小    C:\\  3.427079e+07  2014-10-01\n",
       "1   财务管理系统  CWXT_DB        184     磁盘已使用大小    D:\\  8.026259e+07  2014-10-01\n",
       "2   财务管理系统  CWXT_DB        183        磁盘容量    C:\\  5.232332e+07  2014-10-01\n",
       "3   财务管理系统  CWXT_DB        183        磁盘容量    D:\\  1.572833e+08  2014-10-01\n",
       "4   财务管理系统  CWXT_DB        184     磁盘已使用大小    C:\\  3.432890e+07  2014-10-02"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 一般情况下默认磁盘容量是定值，所以需要剔除磁盘容量重复的数据\n",
    "data.drop_duplicates(data.columns[:-1],inplace=True)\n",
    "data.to_excel('dataCleaned.xlsx')\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#---* 2 *---属性构造 \n",
    "# 思路：由于每台服务器上的这三个属性值一直不变：NAME、TARGET_ID、ENTITY，将这三个属性值合并\n",
    "inputfile2 = 'dataCleaned.xlsx'\n",
    "data = pd.read_excel(inputfile2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SYS_NAME</th>\n",
       "      <th>CWXT_DB:184:C:\\</th>\n",
       "      <th>CWXT_DB:184:D:\\</th>\n",
       "      <th>COLLECTTIME</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>COLLECTTIME</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2014-10-01</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34270787.33</td>\n",
       "      <td>80262592.65</td>\n",
       "      <td>2014-10-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-02</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34328899.02</td>\n",
       "      <td>83200151.65</td>\n",
       "      <td>2014-10-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-03</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34327553.50</td>\n",
       "      <td>83208320.00</td>\n",
       "      <td>2014-10-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-04</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34288672.21</td>\n",
       "      <td>83099271.65</td>\n",
       "      <td>2014-10-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-05</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34190978.41</td>\n",
       "      <td>82765171.65</td>\n",
       "      <td>2014-10-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-06</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34187614.43</td>\n",
       "      <td>82522895.00</td>\n",
       "      <td>2014-10-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-07</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34285280.22</td>\n",
       "      <td>82590885.00</td>\n",
       "      <td>2014-10-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-08</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34290578.41</td>\n",
       "      <td>82368173.30</td>\n",
       "      <td>2014-10-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-09</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>33211870.40</td>\n",
       "      <td>82172263.30</td>\n",
       "      <td>2014-10-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-10</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>33249253.87</td>\n",
       "      <td>81922685.00</td>\n",
       "      <td>2014-10-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-11</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>33253832.53</td>\n",
       "      <td>84844722.95</td>\n",
       "      <td>2014-10-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-12</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34328058.03</td>\n",
       "      <td>84769868.90</td>\n",
       "      <td>2014-10-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-13</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34328674.80</td>\n",
       "      <td>84558703.40</td>\n",
       "      <td>2014-10-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-14</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34234933.61</td>\n",
       "      <td>84207166.80</td>\n",
       "      <td>2014-10-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-15</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34022726.41</td>\n",
       "      <td>84042911.90</td>\n",
       "      <td>2014-10-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-16</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35016309.47</td>\n",
       "      <td>84129516.15</td>\n",
       "      <td>2014-10-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-17</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34981412.82</td>\n",
       "      <td>83877754.85</td>\n",
       "      <td>2014-10-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-18</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34828871.38</td>\n",
       "      <td>83887520.40</td>\n",
       "      <td>2014-10-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-19</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34832868.75</td>\n",
       "      <td>83538509.75</td>\n",
       "      <td>2014-10-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-20</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34843372.75</td>\n",
       "      <td>86483653.00</td>\n",
       "      <td>2014-10-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-21</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34847749.42</td>\n",
       "      <td>82496743.30</td>\n",
       "      <td>2014-10-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-22</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34829775.93</td>\n",
       "      <td>82300356.65</td>\n",
       "      <td>2014-10-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-23</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34824290.49</td>\n",
       "      <td>82130251.65</td>\n",
       "      <td>2014-10-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-24</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34827149.93</td>\n",
       "      <td>84844587.65</td>\n",
       "      <td>2014-10-24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-25</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34820088.89</td>\n",
       "      <td>84845444.65</td>\n",
       "      <td>2014-10-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-26</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34776030.47</td>\n",
       "      <td>84684911.05</td>\n",
       "      <td>2014-10-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-27</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34667722.51</td>\n",
       "      <td>84500606.35</td>\n",
       "      <td>2014-10-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-28</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35705011.09</td>\n",
       "      <td>84145461.25</td>\n",
       "      <td>2014-10-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-29</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35594215.37</td>\n",
       "      <td>84172525.10</td>\n",
       "      <td>2014-10-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-30</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35411287.07</td>\n",
       "      <td>83957818.70</td>\n",
       "      <td>2014-10-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-31</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35389055.02</td>\n",
       "      <td>83684789.75</td>\n",
       "      <td>2014-10-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-11-01</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35385076.36</td>\n",
       "      <td>86485366.95</td>\n",
       "      <td>2014-11-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-11-02</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35462038.02</td>\n",
       "      <td>86454023.45</td>\n",
       "      <td>2014-11-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-11-03</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35486821.33</td>\n",
       "      <td>86127041.70</td>\n",
       "      <td>2014-11-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-11-04</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35471088.77</td>\n",
       "      <td>86161390.40</td>\n",
       "      <td>2014-11-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-11-05</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35547564.55</td>\n",
       "      <td>85938933.90</td>\n",
       "      <td>2014-11-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-11-06</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35606941.11</td>\n",
       "      <td>85645056.50</td>\n",
       "      <td>2014-11-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-11-07</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35546714.13</td>\n",
       "      <td>85272926.05</td>\n",
       "      <td>2014-11-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-11-08</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35510966.73</td>\n",
       "      <td>88110097.75</td>\n",
       "      <td>2014-11-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-11-09</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35491498.51</td>\n",
       "      <td>88128626.65</td>\n",
       "      <td>2014-11-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-11-10</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35601990.55</td>\n",
       "      <td>88075997.75</td>\n",
       "      <td>2014-11-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-11-11</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35687972.60</td>\n",
       "      <td>87753526.65</td>\n",
       "      <td>2014-11-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-11-12</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35704312.58</td>\n",
       "      <td>87249335.55</td>\n",
       "      <td>2014-11-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-11-13</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>35704980.73</td>\n",
       "      <td>86986142.20</td>\n",
       "      <td>2014-11-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-11-14</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34570385.45</td>\n",
       "      <td>86678240.00</td>\n",
       "      <td>2014-11-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-11-15</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34673820.69</td>\n",
       "      <td>89766600.00</td>\n",
       "      <td>2014-11-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-11-16</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34793245.31</td>\n",
       "      <td>89377527.25</td>\n",
       "      <td>2014-11-16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            SYS_NAME  CWXT_DB:184:C:\\  CWXT_DB:184:D:\\ COLLECTTIME\n",
       "COLLECTTIME                                                       \n",
       "2014-10-01    财务管理系统      34270787.33      80262592.65  2014-10-01\n",
       "2014-10-02    财务管理系统      34328899.02      83200151.65  2014-10-02\n",
       "2014-10-03    财务管理系统      34327553.50      83208320.00  2014-10-03\n",
       "2014-10-04    财务管理系统      34288672.21      83099271.65  2014-10-04\n",
       "2014-10-05    财务管理系统      34190978.41      82765171.65  2014-10-05\n",
       "2014-10-06    财务管理系统      34187614.43      82522895.00  2014-10-06\n",
       "2014-10-07    财务管理系统      34285280.22      82590885.00  2014-10-07\n",
       "2014-10-08    财务管理系统      34290578.41      82368173.30  2014-10-08\n",
       "2014-10-09    财务管理系统      33211870.40      82172263.30  2014-10-09\n",
       "2014-10-10    财务管理系统      33249253.87      81922685.00  2014-10-10\n",
       "2014-10-11    财务管理系统      33253832.53      84844722.95  2014-10-11\n",
       "2014-10-12    财务管理系统      34328058.03      84769868.90  2014-10-12\n",
       "2014-10-13    财务管理系统      34328674.80      84558703.40  2014-10-13\n",
       "2014-10-14    财务管理系统      34234933.61      84207166.80  2014-10-14\n",
       "2014-10-15    财务管理系统      34022726.41      84042911.90  2014-10-15\n",
       "2014-10-16    财务管理系统      35016309.47      84129516.15  2014-10-16\n",
       "2014-10-17    财务管理系统      34981412.82      83877754.85  2014-10-17\n",
       "2014-10-18    财务管理系统      34828871.38      83887520.40  2014-10-18\n",
       "2014-10-19    财务管理系统      34832868.75      83538509.75  2014-10-19\n",
       "2014-10-20    财务管理系统      34843372.75      86483653.00  2014-10-20\n",
       "2014-10-21    财务管理系统      34847749.42      82496743.30  2014-10-21\n",
       "2014-10-22    财务管理系统      34829775.93      82300356.65  2014-10-22\n",
       "2014-10-23    财务管理系统      34824290.49      82130251.65  2014-10-23\n",
       "2014-10-24    财务管理系统      34827149.93      84844587.65  2014-10-24\n",
       "2014-10-25    财务管理系统      34820088.89      84845444.65  2014-10-25\n",
       "2014-10-26    财务管理系统      34776030.47      84684911.05  2014-10-26\n",
       "2014-10-27    财务管理系统      34667722.51      84500606.35  2014-10-27\n",
       "2014-10-28    财务管理系统      35705011.09      84145461.25  2014-10-28\n",
       "2014-10-29    财务管理系统      35594215.37      84172525.10  2014-10-29\n",
       "2014-10-30    财务管理系统      35411287.07      83957818.70  2014-10-30\n",
       "2014-10-31    财务管理系统      35389055.02      83684789.75  2014-10-31\n",
       "2014-11-01    财务管理系统      35385076.36      86485366.95  2014-11-01\n",
       "2014-11-02    财务管理系统      35462038.02      86454023.45  2014-11-02\n",
       "2014-11-03    财务管理系统      35486821.33      86127041.70  2014-11-03\n",
       "2014-11-04    财务管理系统      35471088.77      86161390.40  2014-11-04\n",
       "2014-11-05    财务管理系统      35547564.55      85938933.90  2014-11-05\n",
       "2014-11-06    财务管理系统      35606941.11      85645056.50  2014-11-06\n",
       "2014-11-07    财务管理系统      35546714.13      85272926.05  2014-11-07\n",
       "2014-11-08    财务管理系统      35510966.73      88110097.75  2014-11-08\n",
       "2014-11-09    财务管理系统      35491498.51      88128626.65  2014-11-09\n",
       "2014-11-10    财务管理系统      35601990.55      88075997.75  2014-11-10\n",
       "2014-11-11    财务管理系统      35687972.60      87753526.65  2014-11-11\n",
       "2014-11-12    财务管理系统      35704312.58      87249335.55  2014-11-12\n",
       "2014-11-13    财务管理系统      35704980.73      86986142.20  2014-11-13\n",
       "2014-11-14    财务管理系统      34570385.45      86678240.00  2014-11-14\n",
       "2014-11-15    财务管理系统      34673820.69      89766600.00  2014-11-15\n",
       "2014-11-16    财务管理系统      34793245.31      89377527.25  2014-11-16"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 教材上方法一：\n",
    "df = data[data['TARGET_ID'] == 184].copy() # 只选取TARGET_ID为184的数据\n",
    "df_group = df.groupby('COLLECTTIME') # 以时间分组得到一个GroupBy对象\n",
    "\n",
    "#定义属性变换函数\n",
    "def attr_trans(x):\n",
    "    result = Series(index = ['SYS_NAME','CWXT_DB:184:C:\\\\','CWXT_DB:184:D:\\\\','COLLECTTIME'])\n",
    "    result['SYS_NAME'] = x['SYS_NAME'].iloc[0]\n",
    "    result['COLLECTTIME'] = x['COLLECTTIME'].iloc[0]\n",
    "    result['CWXT_DB:184:C:\\\\'] = x['VALUE'].iloc[0]\n",
    "    result['CWXT_DB:184:D:\\\\'] = x['VALUE'].iloc[1]\n",
    "    return result \n",
    "    \n",
    "data_attr_constr = df_group.apply(attr_trans)# 逐组处理\n",
    "data_attr_constr.to_excel('attrsConstruction.xlsx',index=False)\n",
    "data_attr_constr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SYS_NAME</th>\n",
       "      <th>CWXT_DB:184:C:\\</th>\n",
       "      <th>CWXT_DB:184:D:\\</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>COLLECTTIME</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2014-10-01</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34270787.33</td>\n",
       "      <td>80262592.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-02</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34328899.02</td>\n",
       "      <td>83200151.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-03</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34327553.50</td>\n",
       "      <td>83208320.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-04</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34288672.21</td>\n",
       "      <td>83099271.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-05</th>\n",
       "      <td>财务管理系统</td>\n",
       "      <td>34190978.41</td>\n",
       "      <td>82765171.65</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            SYS_NAME  CWXT_DB:184:C:\\  CWXT_DB:184:D:\\\n",
       "COLLECTTIME                                           \n",
       "2014-10-01    财务管理系统      34270787.33      80262592.65\n",
       "2014-10-02    财务管理系统      34328899.02      83200151.65\n",
       "2014-10-03    财务管理系统      34327553.50      83208320.00\n",
       "2014-10-04    财务管理系统      34288672.21      83099271.65\n",
       "2014-10-05    财务管理系统      34190978.41      82765171.65"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#方法二，死方法，没有方法一灵活\n",
    "df_g = df.groupby('COLLECTTIME').size()\n",
    "indexpre = df_g.index\n",
    "data_processed = DataFrame([],index = indexpre, columns=['SYS_NAME','CWXT_DB:184:C:\\\\','CWXT_DB:184:D:\\\\'])\n",
    "data_processed['SYS_NAME'] =  u'财务管理系统'\n",
    "data_processed['CWXT_DB:184:C:\\\\'] = df['VALUE'][df['ENTITY']=='C:\\\\'].values\n",
    "data_processed['CWXT_DB:184:D:\\\\'] = df['VALUE'][df['ENTITY']=='D:\\\\'].values\n",
    "data_processed.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
